﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace StudentManage
{
    public partial class FrmClassList : Form
    {
        public FrmClassList()
        {
            InitializeComponent();
        }
        /// <summary>
        /// 初始化班级列表页面
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void FrmClassList_Load(object sender, EventArgs e)
        {
            //初始化年级列表
            InitGradeComBox();
            //初始化班级列表
            InitAllClassList();             
        }

        private void InitAllClassList()
        {
            string sqlClass = "SELECT c.`ClassId`,c.`ClassName`,g.`GradeName`,c.`Remark` " +
                         "FROM classinfo c JOIN gradeinfo g ON c.`GradeId`= g.`GradeId`";
            //填充班级列表数据
            DataTable dtCalssList = SqlHelper.GetDataTable(sqlClass);
            dgvClassList.DataSource = dtCalssList;
        }

        private void InitGradeComBox()
        {
            //填充年级combox
            string sqlGrade = "select GradeId,GradeName from GradeInfo";
            DataTable dtGradeCbo = SqlHelper.GetDataTable(sqlGrade);
            //创建一个‘请选择’的行，用于默认选择
            DataRow dr = dtGradeCbo.NewRow();
            dr["GradeName"] = "请选择";
            dr["GradeId"] = 0;
            //dtGradeCbo.Rows.Add(dr); //默认添加到最后一行
            dtGradeCbo.Rows.InsertAt(dr,0);
            cboGrades.DataSource = dtGradeCbo;
            cboGrades.DisplayMember = "GradeName";
            cboGrades.ValueMember = "GradeId";
            //默认第一次加载index= 0
            cboGrades.SelectedIndex = 0;
        }

        /// <summary>
        /// 模糊查询（按条件查询）
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnFind_Click(object sender, EventArgs e)
        {
            //获取条件值
            int gradeId = (int)cboGrades.SelectedValue;
            string className = txtClassName.Text.Trim();
            string sql = "SELECT c.`ClassId`,c.`ClassName`,g.`GradeName`,c.`Remark` " +
                         "FROM classinfo c JOIN gradeinfo g ON c.`GradeId`= g.`GradeId`";
            sql += "where 1=1 ";
            if (gradeId > 0)
            {
                sql += " and c.`GradeId`= @GradeId";
            }
            if (!string.IsNullOrEmpty(className))
            {
                sql += " and c.`ClassName`like @ClassName";
            }
            //设置参数
            MySqlParameter[] paras =
            {
            new MySqlParameter("@GradeId",gradeId),
            new MySqlParameter("@ClassName","%"+className+"%")
            };
            DataTable conditionQueryResult = SqlHelper.GetDataTable(sql,paras);
            dgvClassList.DataSource = conditionQueryResult;
        }
    }
}
